package cn.fldong.dao.impl;

import cn.fldong.dao.ClassDAO;
import cn.fldong.model.ClassInfo;
import cn.fldong.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassDAO接口的实现类，提供班级相关的数据库操作实现
 */
public class ClassDAOImpl implements ClassDAO {
    private static final String GET_CLASS_BY_ID = "SELECT * FROM classes WHERE class_id = ?";
    private static final String GET_ALL_CLASSES = "SELECT * FROM classes ORDER BY class_id";
    private static final String GET_CLASSES_BY_GRADE = "SELECT * FROM classes WHERE grade = ? ORDER BY class_id";
    private static final String GET_CLASSES_BY_MAJOR = "SELECT * FROM classes WHERE major = ? ORDER BY class_id";
    private static final String ADD_CLASS = "INSERT INTO classes(class_id, class_name, grade, major, head_teacher, head_teacher_phone, head_teacher_email, classroom, max_student_count, create_date, class_description) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_CLASS = "UPDATE classes SET class_name=?, grade=?, major=?, head_teacher=?, head_teacher_phone=?, head_teacher_email=?, classroom=?, max_student_count=?, create_date=?, class_description=? WHERE class_id=?";
    private static final String DELETE_CLASS = "DELETE FROM classes WHERE class_id = ?";
    private static final String CLASS_NAME_EXISTS = "SELECT COUNT(*) FROM classes WHERE class_name = ?";
    private static final String HAS_STUDENTS = "SELECT COUNT(*) FROM students WHERE class_id = ?";
    private static final String SEARCH_BY_CLASS_NAME = "SELECT * FROM classes WHERE class_name LIKE ? ORDER BY class_id";
    private static final String SEARCH_BY_CLASS_ID = "SELECT * FROM classes WHERE class_id LIKE ? ORDER BY class_id";
    private static final String SEARCH_BY_MAJOR = "SELECT * FROM classes WHERE major LIKE ? ORDER BY class_id";
    private static final String SEARCH_BY_GRADE = "SELECT * FROM classes WHERE grade LIKE ? ORDER BY class_id";

    /**
     * 将ResultSet映射为ClassInfo对象的私有辅助方法
     */
    private ClassInfo mapResultSetToClassInfo(ResultSet rs) throws SQLException {
        ClassInfo classInfo = new ClassInfo();
        classInfo.setClassId(rs.getString("class_id"));
        classInfo.setClassName(rs.getString("class_name"));
        classInfo.setGrade(rs.getString("grade"));
        classInfo.setMajor(rs.getString("major"));
        classInfo.setHeadTeacher(rs.getString("head_teacher"));
        classInfo.setHeadTeacherPhone(rs.getString("head_teacher_phone"));
        classInfo.setHeadTeacherEmail(rs.getString("head_teacher_email"));
        classInfo.setClassroom(rs.getString("classroom"));
        classInfo.setMaxStudentCount(rs.getInt("max_student_count"));
        classInfo.setCreateDate(rs.getDate("create_date"));
        classInfo.setClassDescription(rs.getString("class_description"));
        classInfo.setCreatedAt(rs.getTimestamp("created_at"));
        classInfo.setUpdatedAt(rs.getTimestamp("updated_at"));
        return classInfo;
    }

    @Override
    public ClassInfo getClassById(String classId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ClassInfo classInfo = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_CLASS_BY_ID);
            stmt.setString(1, classId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                classInfo = mapResultSetToClassInfo(rs);
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return classInfo;
    }

    @Override
    public List<ClassInfo> getAllClasses() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<ClassInfo> classList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_ALL_CLASSES);
            rs = stmt.executeQuery();

            while (rs.next()) {
                classList.add(mapResultSetToClassInfo(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return classList;
    }

    @Override
    public List<ClassInfo> getClassesByGrade(String grade) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<ClassInfo> classList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_CLASSES_BY_GRADE);
            stmt.setString(1, grade);
            rs = stmt.executeQuery();

            while (rs.next()) {
                classList.add(mapResultSetToClassInfo(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return classList;
    }

    @Override
    public List<ClassInfo> getClassesByMajor(String major) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<ClassInfo> classList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_CLASSES_BY_MAJOR);
            stmt.setString(1, major);
            rs = stmt.executeQuery();

            while (rs.next()) {
                classList.add(mapResultSetToClassInfo(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return classList;
    }

    @Override
    public boolean addClass(ClassInfo classInfo) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(ADD_CLASS);
            stmt.setString(1, classInfo.getClassId());
            stmt.setString(2, classInfo.getClassName());
            stmt.setString(3, classInfo.getGrade());
            stmt.setString(4, classInfo.getMajor());
            stmt.setString(5, classInfo.getHeadTeacher());
            stmt.setString(6, classInfo.getHeadTeacherPhone());
            stmt.setString(7, classInfo.getHeadTeacherEmail());
            stmt.setString(8, classInfo.getClassroom());
            stmt.setInt(9, classInfo.getMaxStudentCount());
            stmt.setDate(10, new java.sql.Date(classInfo.getCreateDate().getTime()));
            stmt.setString(11, classInfo.getClassDescription());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean updateClass(ClassInfo classInfo) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(UPDATE_CLASS);
            stmt.setString(1, classInfo.getClassName());
            stmt.setString(2, classInfo.getGrade());
            stmt.setString(3, classInfo.getMajor());
            stmt.setString(4, classInfo.getHeadTeacher());
            stmt.setString(5, classInfo.getHeadTeacherPhone());
            stmt.setString(6, classInfo.getHeadTeacherEmail());
            stmt.setString(7, classInfo.getClassroom());
            stmt.setInt(8, classInfo.getMaxStudentCount());
            stmt.setDate(9, new java.sql.Date(classInfo.getCreateDate().getTime()));
            stmt.setString(10, classInfo.getClassDescription());
            stmt.setString(11, classInfo.getClassId());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean deleteClass(String classId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(DELETE_CLASS);
            stmt.setString(1, classId);
            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean classNameExists(String className) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(CLASS_NAME_EXISTS);
            stmt.setString(1, className);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }
    }

    @Override
    public boolean hasStudents(String classId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(HAS_STUDENTS);
            stmt.setString(1, classId);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }
    }

    @Override
    public List<ClassInfo> searchClassesByClassName(String keyword) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<ClassInfo> classList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(SEARCH_BY_CLASS_NAME);
            stmt.setString(1, "%" + keyword + "%");
            rs = stmt.executeQuery();

            while (rs.next()) {
                classList.add(mapResultSetToClassInfo(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return classList;
    }

    @Override
    public List<ClassInfo> searchClassesByClassId(String keyword) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<ClassInfo> classList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(SEARCH_BY_CLASS_ID);
            stmt.setString(1, "%" + keyword + "%");
            rs = stmt.executeQuery();

            while (rs.next()) {
                classList.add(mapResultSetToClassInfo(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return classList;
    }

    @Override
    public List<ClassInfo> searchClassesByMajor(String keyword) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<ClassInfo> classList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(SEARCH_BY_MAJOR);
            stmt.setString(1, "%" + keyword + "%");
            rs = stmt.executeQuery();

            while (rs.next()) {
                classList.add(mapResultSetToClassInfo(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return classList;
    }

    @Override
    public List<ClassInfo> searchClassesByGrade(String keyword) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<ClassInfo> classList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(SEARCH_BY_GRADE);
            stmt.setString(1, "%" + keyword + "%");
            rs = stmt.executeQuery();

            while (rs.next()) {
                classList.add(mapResultSetToClassInfo(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return classList;
    }
}